package edu.zut.cs.network.dao.impl;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import edu.zut.cs.network.base.BaseMainCase;
import edu.zut.cs.network.dao.BookDao;
import edu.zut.cs.network.entity.Book;
import edu.zut.cs.network.jdbc.JdbcExample;

public class BookDaoImpl extends BaseMainCase implements BookDao {
//	private static String driver;
//	private static String url;
//	private static String username;
//	private static String password;

	ResultSet rs = null;
	PreparedStatement st = null;
	Connection conn = null;

	public BookDaoImpl() throws SQLException {
		
		InputStream is = JdbcExample.class.getResourceAsStream("/dbcp.properties");// 读文件获取账户密码等信息
		Properties prop = new Properties();// 新建一个空属性列表
		try {
			prop.load(is); // 从输入流中读取属性列表（键和元素对）
			DataSource dataSource = BasicDataSourceFactory.createDataSource(prop);//使用dbcp创建数据库连接池
			conn=dataSource.getConnection();//请求从连接池中获取
		} catch (IOException e1) {
			e1.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
//		/*jdbc从文件中获取连接*/
//		driver = prop.getProperty("jdbc.driver");// 用指定的键在此属性列表中搜索属性
//		url = prop.getProperty("jdbc.url");
//		username = prop.getProperty("jdbc.username");
//		password = prop.getProperty("jdbc.password");
//		// 将JDBC驱动加载到JVM中
//		try {
//			Class.forName(driver);
//			conn = DriverManager.getConnection(url, username, password);
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
	}

	@Override
	public List<Book> findByName(String name) {
		List<Book> bookList = new ArrayList<>();
		try {
			String sql = "SELECT BOOKNAME,POSITION,NUMBER,ID FROM BOOK Where BOOKNAME =? ";
			st = conn.prepareStatement(sql);// 创建一个 Statement 对象来将 SQL 语句发送到数据库。
			st.setString(1, name);
			rs = st.executeQuery();// 执行给定的 SQL 语句，该语句返回单个 ResultSet 对象
			while (rs.next()) {
				Book book = new Book(rs.getString(1), rs.getString(2), rs.getString(3), rs.getLong(4));
				bookList.add(book);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				if (st != null) {
					st.close();
				}
//				if (conn != null) {
//					conn.close();
//				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		logger.info(bookList);
		return bookList;
	}

	@Override
	public int insert(List<Book> booklist, String tableName) {
		int[] size = null;
		try {
			PreparedStatement pstmt;
			String sql = "INSERT INTO " + tableName + " (BOOKNAME,POSITION,NUMBER,ID) VALUES(?,?,?,?)";
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			for (int i = 0; i < booklist.size(); i++) {
				pstmt.setString(1, booklist.get(i).getBookName());
				pstmt.setString(2, booklist.get(i).getPosition());
				pstmt.setString(3, booklist.get(i).getNumber());
				pstmt.setLong(4, booklist.get(i).getId());
				pstmt.addBatch();
			}
			size = pstmt.executeBatch();
			logger.info(" insert result: " + size.length);
			pstmt.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		return size.length;
	}

	@Override
	public List<Book> getALl(String tableName) {
		List<Book> bookList = new ArrayList<>();
		try {
			String sql = "SELECT BOOKNAME,POSITION,NUMBER,ID FROM  "+tableName;
			st = conn.prepareStatement(sql);// 创建一个 Statement 对象来将 SQL 语句发送到数据库。
			rs = st.executeQuery();// 执行给定的 SQL 语句，该语句返回单个 ResultSet 对象
			while (rs.next()) {
				Book book = new Book(rs.getString(1), rs.getString(2), rs.getString(3), rs.getLong(4));
				bookList.add(book);
			}
			rs.close();
			st.close();
			//conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bookList;
	}

	@Override
	public int delete(String name) {
		int i = 0;
		try {
			String sql = "DELETE FROM BOOK WHERE BOOKNAME=?";
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, name);
			i = pst.executeUpdate();
			logger.info("delete result: " + i);
			pst.close();
		//	conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public int upDate(List<Book> bookList) {
		int[] size = null;
		try {
			conn.setAutoCommit(false);// 设置事务不自动提交
			String sql = "UPDATE BOOK SET POSITION=? ,NUMBER=? WHERE BOOKNAME=?";
			PreparedStatement pst = conn.prepareStatement(sql);
			for (Book b : bookList) {
				pst.setString(1, b.getPosition());
				pst.setString(2, b.getNumber());
				pst.setString(3, b.getBookName());
				pst.addBatch();
			}
			size = pst.executeBatch();
			conn.commit();
			logger.info("update result: " + size.length);
			pst.close();
		//	conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return size.length;
	}

	@Override
	public Book findById(Long id) {
		Book book = null;
		String sql = "SELECT BOOKNAME,POSITION,NUMBER,ID FROM BOOK WHERE ID=?";
		try {
			st = conn.prepareStatement(sql);
			st.setLong(1, id);
			rs = st.executeQuery();
			while (rs.next()) {
				book = new Book();
				book.setBookName(rs.getString("BOOKNAME"));
				book.setPosition(rs.getString("POSITION"));
				book.setNumber(rs.getString("NUMBER"));
				book.setId(rs.getLong("ID"));
				if (book.getId() == id) {
					logger.info("found book:" + book);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return book;
	}

	@Override
	public void exportToCSV(String filepath, String tableName) {
		try {
			byte[] bs={(byte)0xef,(byte)0xbb,(byte)0xbf};//  UTF-8的 编码  默认无BOM 此行写入文件头变带BOM头
			BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filepath), "UTF-8"));
			List<Book> bookslist = new ArrayList<>();
			bookslist = getALl(tableName);
			out.write(new String(bs));//写入BOM头
			for (Book i : bookslist) {
				out.write(i.getBookName());
				out.write(",");
				out.write(i.getPosition());
				out.write(",");
				out.write(i.getNumber());
				out.write(",");
				out.write(Long.toString(i.getId()));
				out.newLine();
			}
			out.flush();
			out.close();
			logger.info("写入成功！");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void importFromCSV(String filepath, String tableName) {
		createTable(tableName);
		List<Book> bookslist = new ArrayList<>();
		BufferedReader in;
		String line = null;
		String[] onerow;
		try {
			in = new BufferedReader(new InputStreamReader(new FileInputStream(filepath), "UTF-8"));
			while ((line = in.readLine()) != null) {
				Book book = new Book();
				onerow = line.split(","); // 默认分割符为逗号，可以不使用逗号
				List<String> onerowlist = Arrays.asList(onerow);
				book.setBookName(onerowlist.get(0));
				book.setPosition(onerowlist.get(1));
				book.setNumber(onerowlist.get(2));
				book.setId(Long.valueOf(onerowlist.get(3)));
				bookslist.add(book);
			}
			insert(bookslist, tableName);
		} catch (NumberFormatException | IOException e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public void createTable(String tableName) {
		String sql = "CREATE TABLE IF NOT EXISTS " + tableName
				+ "(BOOKNAME VARCHAR(80), POSITION VARCHAR(80),NUMBER VARCHAR(80),ID INT)";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public void exportToJson(String filepath, String tableName) {
		List<Book> booklist=new ArrayList<>();
		booklist=getALl(tableName);
		List<JSON> jsonList=new ArrayList<>();
		jsonList.addAll((Collection<? extends JSON>) JSONArray.toJSON(booklist));
		System.out.println(jsonList);
		
		try {
			BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filepath), "UTF-8"));
			for(JSON i:jsonList) {
				//out.write(JsonFormatTool.formatJson(i.toJSONString()));调用Json工具类格式化Json字符串
				out.write(i.toJSONString());
				out.newLine();
			}			
			out.flush();
			out.close();
		} catch (UnsupportedEncodingException | FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void importFromJson(String filepath, String tableName) {
		List<Book> bookslist=new ArrayList<>();
		BufferedReader in;
		createTable(tableName);
		Book book=new Book();
		String line = null;
		try {
			in = new BufferedReader(new InputStreamReader(new FileInputStream(filepath), "UTF-8"));
			while ((line = in.readLine()) != null) {
				book=JSON.parseObject(line, book.getClass());
				bookslist.add(book);
			}
			in.close();
		} catch (NumberFormatException | IOException e) {
			e.printStackTrace();
		}
		insert(bookslist, tableName);
		System.out.println(bookslist);
	}
}
